﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Reflection;
using System.Xml;
using System.Xml.XPath;
using NHibernate.Cfg;
using NHibernate.Tool.hbm2ddl;

namespace OrderSystem.Core.Utils
{
    public class MsSqlDbUtility
    {
        private const string dataSourcePart = "Data Source";
        private const string initialCatalogPart = "Initial Catalog";
        private const string integrateSecurityPart = "Integrated Security";
        private const string userNamePart = "User";
        private const string passwordPart = "Password";

        private static void SqlServerExists(string masterConnectionString)
        {
            SqlConnection connection = new SqlConnection(masterConnectionString);
            try
            {
                connection.Open();
                connection.Close();
            }
            catch (Exception err)
            {
                throw new ApplicationException(
                    string.Format("Ошибка подключения к SQL серверу.\nТекущая строка подключения:{0}\n\n{1}",
                                  masterConnectionString, err.Message));
            }
        }

        private static bool DbExists(string masterConnectionString, string dbName)
        {
            bool result = false;
            string existsDbScript =
                string.Format(
                    "USE [master] IF EXISTS (SELECT name FROM sys.databases WHERE name = N'{0}') select 1 else select 0",
                    dbName);

            SqlConnection connection = new SqlConnection(masterConnectionString);
            try
            {
                connection.Open();

                var sqlResult = new SqlCommand(existsDbScript, connection).ExecuteScalar();

                if (sqlResult != null && sqlResult is Int32)
                {
                    result = (int) sqlResult == 1 ? true : false;
                }

                connection.Close();
            }
            catch (Exception err)
            {
                throw new ApplicationException(string.Format(
                                                   "Текущая строка подключения:{0}\nСоздаваемая БД:{1}\n\n{2}",
                                                   masterConnectionString, dbName, err.Message));
            }

            return result;
        }

        private static void CreateDb(string masterConnectionString, string dbName)
        {
            string createDbScript = string.Format("USE [master] CREATE DATABASE [{0}]", dbName);

            SqlConnection connection = new SqlConnection(masterConnectionString);
            try
            {
                connection.Open();
                new SqlCommand(createDbScript, connection).ExecuteNonQuery();
                connection.Close();
            }
            catch (Exception err)
            {
                throw new ApplicationException(
                    string.Format("Ошибка создания БД.\nТекущая строка подключения:{0}\nСоздаваемая БД:{1}\n\n{2}",
                                  masterConnectionString, dbName, err.Message));
            }
        }

        private static string GetPartConnectionString(string sqlConnectionString, string valueHeader)
        {
            if (string.IsNullOrEmpty(sqlConnectionString))
            {
                throw new ArgumentNullException(sqlConnectionString);
            }

            IEnumerable<string> items = sqlConnectionString.Split(';');

            string itemConnectionString = "";

            foreach (var item in items)
            {
                if (item.ToLowerInvariant().Contains(valueHeader.ToLowerInvariant()))
                {
                    itemConnectionString = item;
                    break;
                }
            }

            if (string.IsNullOrEmpty(itemConnectionString))
            {
                return "";
            }

            string[] para = itemConnectionString.Split('=');


            if (para.Length < 2 || String.IsNullOrEmpty(para[1]))
            {
                if (!String.Equals(valueHeader, passwordPart, StringComparison.InvariantCultureIgnoreCase))
                {
                    return "";
                }
            }

            return para[1]; //.ToLowerInvariant();
        }

        public static string GetConnectionString(string path2config)
        {
            string result = "";

            using (var stream = File.OpenRead(path2config))
            {
                var pathNavigator = new XPathDocument(XmlReader.Create(stream)).CreateNavigator();

                const string xPathExpressionString =
                    @"/configuration/castle/facilities/facility[@id='nhibernate']/factory[@id='mssql.factory']/settings/item[@key='connection.connection_string']";

                XPathNodeIterator nodes = pathNavigator.Select(xPathExpressionString);

                while (nodes.MoveNext())
                {
                    result = nodes.Current.Value;
                    break;
                }
            }

            return result;
        }

        public static void CreateDbProcessing(string pathToConfigFile)
        {
            string connectionStringFromConfig = GetConnectionString(pathToConfigFile);
            string masterConnectionString = GetMasterConnectionString(connectionStringFromConfig);

            string dbName = GetPartConnectionString(connectionStringFromConfig, initialCatalogPart);

            SqlServerExists(masterConnectionString);

            if (!DbExists(masterConnectionString, dbName))
            {
                CreateDb(masterConnectionString, dbName);

                try
                {
                    CreateDbStructure(connectionStringFromConfig);
                }
                catch (Exception err)
                {
                    DropDb(masterConnectionString, dbName);
                    throw new ApplicationException(
                        string.Format("Ошибка создания структуры БД.\nСтрока подключения:{0}\n\n{1}",
                                      connectionStringFromConfig, err.Message));
                }
            }
        }

        public static void CreateDbOnly(string connectionStringFromConfig, bool dropDb)
        {
            string masterConnectionString = GetMasterConnectionString(connectionStringFromConfig);

            string dbName = GetPartConnectionString(connectionStringFromConfig, initialCatalogPart);

            SqlServerExists(masterConnectionString);

            if (DbExists(masterConnectionString, dbName))
            {
                if (dropDb)
                {
                    DropDb(masterConnectionString, dbName);
                    CreateDb(masterConnectionString, dbName);
                }
            }
            else
            {
                CreateDb(masterConnectionString, dbName);
            }
        }

        private static void CreateDbStructure(string connectionString)
        {
            const string connectionProvider = "NHibernate.Connection.DriverConnectionProvider";
            const string dialect = "NHibernate.Dialect.MsSql2005Dialect";
            const string connectionDriverClass = "NHibernate.Driver.SqlClientDriver";
            const string delimiter = "GO";

            try
            {
                Configuration config = new Configuration();
                Dictionary<string, string> properties = new Dictionary<string, string>
                                                            {
                                                                {"dialect", dialect},
                                                                {"connection.provider", connectionProvider},
                                                                {"connection.driver_class", connectionDriverClass},
                                                                {"connection.connection_string", connectionString}
                                                            };

                config.AddProperties(properties);

                config.AddAssembly(Assembly.GetExecutingAssembly());

                new SchemaExport(config).Execute(false, true, false);
            }
            catch (Exception err)
            {
                throw new ApplicationException(err.Message, err);
            }
        }

        private static string GetMasterConnectionString(string connectionStringFromConfig)
        {
            string serverName = GetPartConnectionString(connectionStringFromConfig, dataSourcePart);

            bool integrateSecurity;
            if (
                !bool.TryParse(GetPartConnectionString(connectionStringFromConfig, integrateSecurityPart),
                               out integrateSecurity))
            {
                integrateSecurity = false;
            }

            string userName = GetPartConnectionString(connectionStringFromConfig, userNamePart);
            string password = GetPartConnectionString(connectionStringFromConfig, passwordPart);


            if (integrateSecurity)
            {
                return string.Format("{0}={1};{2}={3};{4}={5}", dataSourcePart, serverName,
                                     initialCatalogPart, "master", integrateSecurityPart,
                                     integrateSecurity);
            }

            return string.Format("{0}={1};{2}={3};{4}={5};{6}={7}", dataSourcePart, serverName,
                                 initialCatalogPart, "master", userNamePart, userName, passwordPart,
                                 password);
        }

        private static void DropDb(string masterConnectionString, string dbName)
        {
            string existsDbScript =
                string.Format(
                    "USE [master] IF EXISTS (SELECT name FROM sys.databases WHERE name = N'{0}') DROP DATABASE [{1}]",
                    dbName, dbName);

            SqlConnection connection = new SqlConnection(masterConnectionString);
            try
            {
                connection.Open();

                new SqlCommand(existsDbScript, connection).ExecuteNonQuery();

                connection.Close();
            }
            catch (Exception err)
            {
                throw new ApplicationException(err.Message);
            }
        }
    }
}